import openpyxl
import datetime
import time
import os
import sys
import logging
import configparser
import json
import ChengBanBuMen
import ReplaceBuMen
from hanchlogger import logger
from openpyxl.cell import MergedCell
from openpyxl.styles import Alignment, Font
from openpyxl.utils import get_column_letter
import numpy as np
import pandas as pd
import hanchxl
import docx

# import GenerateCharts
# from docx.shared import RGBColor
# from docxcompose.composer import Composer

timeCur = datetime.datetime.now()
# dirPath = os.path.dirname(os.path.realpath(sys.executable))
dirPath = 'E:\\日常工作\\数据组日常工作-韩冲\\每日失分工单明细'
dirPathShare = '\\\\172.16.10.175\\共享文件\\数据组\\区级考核裸分情况统计表'
dirPathHuiZong = '\\\\172.16.10.175\\共享文件\\数据组\\2023年文件\\杨书记调度会\\书记批示工单进展汇总'

logFile = dirPath + '\\ShiFenInFo.log'
logger.get_log(logFile, 'LoggerShiFenInFo', logging.DEBUG)

iniFilePathCur = dirPath + '\\autoUnresolvedList.ini'
iniConfig = configparser.ConfigParser(allow_no_value=True)
iniConfig.read(iniFilePathCur, encoding='utf-8')

timeYesterdayStr = datetime.datetime.fromtimestamp(timeCur.timestamp() - 60 * 60 * 24).strftime('%Y-%m-%d')
timeYesterdayStr = timeYesterdayStr + '  {}:{}:{}'.format(timeCur.hour, timeCur.minute, timeCur.second)
datatimeYesterday = datetime.datetime.strptime(timeYesterdayStr, '%Y-%m-%d %H:%M:%S')
timeYesterday = time.strptime(timeYesterdayStr, '%Y-%m-%d %H:%M:%S')
timeYesterdayStamp = int(time.mktime(timeYesterday))
logger.debug('昨天（日期）：{}    昨天（时间戳）：{}'.format(timeYesterdayStr, timeYesterdayStamp))


def get_config_value(ini_config, section, section_key, value_default):
    sectionValue = value_default
    if ini_config.has_section(section):
        if ini_config.has_option(section, section_key):
            sectionValue = ini_config.get(section, section_key, )
            if sectionValue is not None and sectionValue != '':
                sectionValue = '{}'.format(sectionValue)
    return sectionValue


# 是否自定义工作目录
customizeWorkFolder = get_config_value(iniConfig, 'global', 'isCustomizeWorkFolder', '0')
if customizeWorkFolder != '0':
    workFolder = get_config_value(iniConfig, 'global', 'workFolder', dirPath)
    if workFolder != '' and dirPath != workFolder:
        if os.path.exists(workFolder):
            dirPath = workFolder
            logger.debug('是否自定义工作目录为：{}'.format(dirPath))
        else:
            logger.debug('自定义的工作目录不存在，工作目录：{}'.format(dirPath))
    else:
        logger.debug('工作目录未填写或者为空，或者与默认值相同，使用默认值：{}'.format(dirPath))
else:
    logger.debug('不使用自定义的工作目录，使用默认值:{}'.format(dirPath))

logger.debug('是否处理今日派单工单列表')
isProcessReceiving = get_config_value(iniConfig, 'global', 'isProcessReceiving', '1')
if isProcessReceiving == '0':
    isProcessReceiving = False
    logger.debug('不处理今日派单工单列表，isProcessUnresolved = False')
else:
    isProcessReceiving = True
    logger.debug('处理今日派单工单列表，isProcessUnresolved = True')

# 是否处理未处理工单列表
isProcessUnresolved = get_config_value(iniConfig, 'global', 'isProcessUnresolved', '1')
if isProcessUnresolved == '0':
    isProcessUnresolved = False
    logger.debug('不处理未处理工单列表，isProcessUnresolved = False')
else:
    isProcessUnresolved = True
    logger.debug('处理未处理工单列表，isProcessUnresolved = True')

OrderCountFolder = get_config_value(iniConfig, 'global', 'OrderCountFolder', '')
if OrderCountFolder is not None and OrderCountFolder != '':
    dirPathShare = dirPath + OrderCountFolder
logger.debug('汇总文件目录：{}'.format(dirPathShare))

fileQuXiTongDaoChu = dirPath + '\\导出工单.xlsx'
fileNameSave = '\\{}.{} 12时-{}.{} 12时失分工单明细.xlsx'.format(datatimeYesterday.month, datatimeYesterday.day,
                                                                 timeCur.month, timeCur.day)
fileResultSave = dirPath + fileNameSave
fileResultMuBanSave = dirPath + '\\失分工单-登记单.xlsx'
fileResultMuBanSave2 = dirPath + '\\失分工单-登记单-2.xlsx'
fileGongDanQuanBu = dirPath + '\\全部工单.xlsx'
fileChengBan = dirPath + '\\承办部门.xlsx'
fileFanKuiQingKuang = dirPath + '\\失分工单督办情况统计表.xlsx'
fileFanKuiQingKuangSave = dirPath + '\\{}月{}日失分工单督办情况统计表.xlsx'.format(timeCur.month, timeCur.day)
fileChengBanBuMen = dirPath + '\\承办部门.xlsx'
fileLuoFenTongJi = dirPathShare + '\\{}.xlsx'.format(timeCur.year)
fileGongDanToday = dirPath + '\\{}.{}-全部工单.xlsx'.format(timeCur.month, timeCur.day)
fileDengJiDanMuBan = dirPath + '\\模板-北京市市民热线服务中心电话登记单.docx'
fileShuJiPiShiHuiZong = dirPathHuiZong + '\\书记批示工单汇总.xlsx'
fileChengBanReplace = dirPath + '\\需替换部门.xlsx'

fileDiaoDu = None
fileDiaoDuLast = None

# 打开替换部门字典表
logger.debug('打开替换部门字典表 文件：{}'.format(fileChengBanReplace))
replaceBuMen = ReplaceBuMen.ReplaceDirectory()
replaceBuMen.load_directory(fileChengBanReplace)
listReplaceBuMen = replaceBuMen.get_replace_directory()

bookQuXiTongDaoChu = openpyxl.load_workbook(fileQuXiTongDaoChu, data_only=True)
sheetQuXiTongDaoChu = bookQuXiTongDaoChu.active
sheetQuXiTongDaoChu.title = '所有工单明细-未排序'
# sheetQuXiTongDaoChu.title = '所有工单明细'
logger.debug(sheetQuXiTongDaoChu.max_row)  # 打印表的行数
logger.debug(sheetQuXiTongDaoChu.max_column)  # 打印表的列数
maxRowQuXiTongDaoChu = sheetQuXiTongDaoChu.max_row
maxColQuXiTongDaoChu = sheetQuXiTongDaoChu.max_column

colHuiFangResult = hanchxl.get_col_index(sheetQuXiTongDaoChu, '回访结果')
sheetQuXiTongDaoChu.delete_cols(colHuiFangResult)

colLaiDianRenIndex = hanchxl.get_col_index(sheetQuXiTongDaoChu, '来电人')
sheetQuXiTongDaoChu.insert_cols(colLaiDianRenIndex, 1)
sheetQuXiTongDaoChu.cell(1, colLaiDianRenIndex).value = '来电人-合并'
# 合并来电人和联系方式
logger.debug('合并来电人和联系方式')
hanchxl.he_bing_cols(sheetQuXiTongDaoChu, '来电人', '来电人电话/账号', '来电人-合并')

logger.debug('移动 截止日期 到 派单时间 后面')
hanchxl.move_col1_to_col2(sheetQuXiTongDaoChu, '截止日期', '派单时间', False)
# logger.debug('移动 参与部门 到 工单编号 后面')
# hanchxl.move_col1_to_col2(sheetQuXiTongDaoChu, '参与部门', '工单编号', False)

colCanYuQuIndex = hanchxl.get_col_index(sheetQuXiTongDaoChu, '参与部门')
sheetQuXiTongDaoChu.cell(1, colCanYuQuIndex).value = '参与部门（区）'

# 插入 承办部门 和 参与部门
logger.debug('插入 承办部门')
colGongDanIndex = hanchxl.get_col_index(sheetQuXiTongDaoChu, '工单编号')
sheetQuXiTongDaoChu.insert_cols(colGongDanIndex + 1, 2)
sheetQuXiTongDaoChu.cell(1, colGongDanIndex + 1).value = '承办部门'
sheetQuXiTongDaoChu.cell(1, colGongDanIndex + 2).value = '参与部门'

maxRowQuXiTongDaoChu = sheetQuXiTongDaoChu.max_row
maxColQuXiTongDaoChu = sheetQuXiTongDaoChu.max_column

# 打开全部工单
bookQuanBuGongDan = openpyxl.load_workbook(fileGongDanQuanBu, data_only=True)
sheetQuanBuGongDan = bookQuanBuGongDan.active
logger.debug(sheetQuanBuGongDan.max_row)  # 打印表的行数
logger.debug(sheetQuanBuGongDan.max_column)  # 打印表的列数
maxRowQuanBuGongDan = sheetQuanBuGongDan.max_row
maxColQuanBuGongDan = sheetQuanBuGongDan.max_column

hanchxl.pi_pei_cheng_ban_qu_xi_tong_2(sheetQuXiTongDaoChu, '主办部门名称', '从办部门名称', '承办部门', '参与部门')
# 根据 ‘工单编号’ 匹配 ‘承办部门’ 和 ‘参与部门’
hanchxl.pi_pei_te_ding_col(sheetQuXiTongDaoChu, '工单编号', sheetQuanBuGongDan, '承办部门', '参与部门')

for buMenInfo in listReplaceBuMen:
    if buMenInfo.isAll is True:
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetQuXiTongDaoChu, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetQuXiTongDaoChu, '参与部门', buMenInfo.nameYuan, buMenInfo.nameNow)
    else:
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetQuXiTongDaoChu, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetQuXiTongDaoChu, '参与部门', buMenInfo.nameYuan, buMenInfo.nameNow)

hanchxl.remove_repeat_value(sheetQuXiTongDaoChu, '承办部门', '参与部门')

bookQuXiTongDaoChu.save(fileResultMuBanSave)
# 删除不必要的列
logger.debug('删除不必要的列')
delColsList = ['工单编号', '承办部门', '参与部门', '标题', '主要内容', '来电人-合并', '办理结果', '是否解决',
               '是否满意', '是否联系', '是否见面', '回访状态', '派单时间', '截止日期', '派单类型']

listTitleConf = get_config_value(iniConfig, 'global', 'titleProcessList', '')
if listTitleConf != '':
    delColsList = json.loads(listTitleConf)
logger.debug('listTitle：{}'.format(delColsList))
hanchxl.remove_bu_bi_yao_col(sheetQuXiTongDaoChu, delColsList)

# 插入 主管领导 和 包片领导 列
logger.debug(sheetQuXiTongDaoChu.max_row)  # 打印表的行数
logger.debug(sheetQuXiTongDaoChu.max_column)  # 打印表的列数
sheetQuXiTongDaoChu.insert_cols(2, 3)

logger.debug(sheetQuXiTongDaoChu.max_row)  # 打印表的行数
logger.debug(sheetQuXiTongDaoChu.max_column)  # 打印表的列数
maxRowQuXiTongDaoChu = sheetQuXiTongDaoChu.max_row
maxColQuXiTongDaoChu = sheetQuXiTongDaoChu.max_column

sheetQuXiTongDaoChu.cell(1, 2).value = '片区'
sheetQuXiTongDaoChu.cell(1, 3).value = '主管领导'
sheetQuXiTongDaoChu.cell(1, 4).value = '包片领导'

# 插入 剩余办单天数 列
logger.debug(sheetQuXiTongDaoChu.max_row)  # 打印表的行数
logger.debug(sheetQuXiTongDaoChu.max_column)  # 打印表的列数
maxRowQuXiTongDaoChu = sheetQuXiTongDaoChu.max_row
maxColQuXiTongDaoChu = sheetQuXiTongDaoChu.max_column
sheetQuXiTongDaoChu.cell(1, maxColQuXiTongDaoChu + 1).value = '截止剩余时间'

maxRowQuXiTongDaoChu = sheetQuXiTongDaoChu.max_row
maxColQuXiTongDaoChu = sheetQuXiTongDaoChu.max_column

timeCurStr = datetime.datetime.fromtimestamp(timeCur.timestamp()).strftime('%Y-%m-%d')
timeCurStr = timeCurStr + '  23:59:59'
timeTodayArray = time.strptime(timeCurStr, '%Y-%m-%d %H:%M:%S')
todayStamp = int(time.mktime(timeTodayArray))
logger.debug('今天（日期）：{}    今天（时间戳）：{}'.format(timeCurStr, todayStamp))

colJieZhiRiQi = hanchxl.get_col_index(sheetQuXiTongDaoChu, '截止日期')
colShengYuTianShu = hanchxl.get_col_index(sheetQuXiTongDaoChu, '截止剩余时间')
logger.debug('截止日期: {} 列   剩余办单天数:{} 列'.format(colJieZhiRiQi, colShengYuTianShu))
for rowSource in range(2, maxRowQuXiTongDaoChu + 1):
    cellValueJieZhi = sheetQuXiTongDaoChu.cell(rowSource, colJieZhiRiQi).value
    if cellValueJieZhi is not None:
        jieZhiArray = time.strptime(cellValueJieZhi, '%Y-%m-%d %H:%M:%S')
        jieZhiStamp = int(time.mktime(jieZhiArray))
        tianShuShengYu = round((jieZhiStamp - todayStamp) / 3600 / 24)
        if tianShuShengYu == 0:
            sheetQuXiTongDaoChu.cell(rowSource, colShengYuTianShu).value = '今日到期'
        elif tianShuShengYu < 0:
            sheetQuXiTongDaoChu.cell(rowSource, colShengYuTianShu).value = '已过期'
        else:
            sheetQuXiTongDaoChu.cell(rowSource, colShengYuTianShu).value = '剩{}日到期'.format(tianShuShengYu)

numShiPai = 0
numQuPai = 0
numShiFen = 0

listLuoFenTongJiTitle = ['日期', '接单总量', '考核工单', '市派', '区派', '联系率', '解决率', '满意率', '裸分得分',
                         '裸分失分']
# 打开 区级考核裸分情况统计表 2023.xlsx
bookKaoHe = None
sheetKaoHe = None
curSheetName = ''
if timeCur.day >= 19:
    curSheetName = '{}月'.format(timeCur.month + 1)
else:
    curSheetName = '{}月'.format(timeCur.month)
if os.path.exists(fileLuoFenTongJi):
    bookKaoHe = openpyxl.load_workbook(fileLuoFenTongJi, data_only=True)
    sheet_names = bookKaoHe.sheetnames
    if curSheetName in sheet_names:
        sheetKaoHe = bookKaoHe[curSheetName]
        maxRowTongJi = sheetKaoHe.max_row
        todayStr = '{}-{}-{}'.format(timeCur.year, timeCur.month, timeCur.day)
        logger.debug('todayStr:{}  '.format(todayStr))
        colToday = hanchxl.get_col_index(sheetKaoHe, '日期')
        colQuPai = hanchxl.get_col_index(sheetKaoHe, '区派工单')
        colShiPai = hanchxl.get_col_index(sheetKaoHe, '市派工单')
        colShiFen = hanchxl.get_col_index(sheetKaoHe, '裸分失分')
        for col in range(2, maxRowTongJi + 1):
            valueToday = sheetKaoHe.cell(col, colToday).value
            if valueToday is not None and valueToday == todayStr:
                numShiPai = sheetKaoHe.cell(col, colShiPai).value
                numQuPai = sheetKaoHe.cell(col, colQuPai).value
                numShiFen = sheetKaoHe.cell(col, colShiFen).value
                break
    else:
        logger.debug("未找到 {} Sheet ".format(curSheetName))
else:
    logger.debug("文件{} 不存在 ".format(fileLuoFenTongJi))

logger.debug('numShiPai:{}  numQuPai:{} numShiFen:{}'.format(numShiPai, numQuPai, numShiFen))

listChengBan = []
listChengBanCount = {}
baoPianLingDao = {}
baoCunLingDao = {}
listBaoPianLingDao = []
listBaoCunLingDao = []
colShiFouJieJueAll = hanchxl.get_col_index(sheetQuXiTongDaoChu, '是否解决')
colShiFouManYiAll = hanchxl.get_col_index(sheetQuXiTongDaoChu, '是否满意')
colShiFouLianXiAll = hanchxl.get_col_index(sheetQuXiTongDaoChu, '是否联系')
colChengBanBuMen = hanchxl.get_col_index(sheetQuXiTongDaoChu, '承办部门')

chengBan = ChengBanBuMen.ChengBanDirectory()
chengBan.load_cheng_ban_list(fileChengBan)
listChengBanKeShi = chengBan.get_list_ke_shi()

for index in listChengBanKeShi:
    logger.debug('承办部门-科室：{} 负责人：{} 主管领导：{}'.format(index, listChengBanKeShi[index].fuZeRen,
                                                                 listChengBanKeShi[index].zhuGuanLingDao))

# 匹配 科室 主管领导
for row in range(2, maxRowQuXiTongDaoChu + 1):
    nameSheQuSearch = sheetQuXiTongDaoChu.cell(row, colChengBanBuMen).value
    if nameSheQuSearch is not None and nameSheQuSearch.strip() != '':
        nameSheQuSearch = nameSheQuSearch.strip()
        if nameSheQuSearch.find('综合城管') != -1:
            nameSheQuSearch = '综合行政执法队'
        if nameSheQuSearch.find('流动人口管理组') != -1:
            nameSheQuSearch = '社会治理组'
        if nameSheQuSearch in listChengBanKeShi:
            lingDaoNameStr = listChengBanKeShi[nameSheQuSearch].zhuGuanLingDao
            sheetQuXiTongDaoChu.cell(row, 4).value = lingDaoNameStr
            sheetQuXiTongDaoChu.cell(row, 3).value = lingDaoNameStr
            sheetQuXiTongDaoChu.cell(row, 2).value = '科室'
            valueCurCellShiFouJieJue = sheetQuXiTongDaoChu.cell(row, colShiFouJieJueAll).value
            valueCurCellShiFouManYi = sheetQuXiTongDaoChu.cell(row, colShiFouManYiAll).value
            valueCurCellShiFouLianXi = sheetQuXiTongDaoChu.cell(row, colShiFouLianXiAll).value
            if valueCurCellShiFouJieJue is not None and valueCurCellShiFouManYi is not None \
                    and valueCurCellShiFouLianXi is not None:
                valueCurCellShiFouJieJue = valueCurCellShiFouJieJue.strip()
                valueCurCellShiFouManYi = valueCurCellShiFouManYi.strip()
                valueCurCellShiFouLianXi = valueCurCellShiFouLianXi.strip()
                if valueCurCellShiFouJieJue == '未解决' or valueCurCellShiFouManYi == '基本满意' \
                        or valueCurCellShiFouManYi == '不满意' or valueCurCellShiFouManYi == '非常不满意':
                    if nameSheQuSearch in listChengBan:
                        listChengBanCount[nameSheQuSearch] = listChengBanCount[nameSheQuSearch] + 1
                    else:
                        listChengBan.append(nameSheQuSearch)
                        listChengBanCount[nameSheQuSearch] = 1
                    baoPianLingDao[nameSheQuSearch] = lingDaoNameStr
                    baoCunLingDao[nameSheQuSearch] = lingDaoNameStr
                    if lingDaoNameStr not in listBaoPianLingDao:
                        listBaoPianLingDao.append(lingDaoNameStr)
                    if lingDaoNameStr not in listBaoCunLingDao:
                        listBaoCunLingDao.append(lingDaoNameStr)
                    logger.debug('----------------------{} {} {} {}'.format(nameSheQuSearch,
                                                                            valueCurCellShiFouJieJue,
                                                                            valueCurCellShiFouManYi,
                                                                            valueCurCellShiFouLianXi))

listChengBanSheQu = chengBan.get_list_she_qu()
for index in listChengBanSheQu:
    logger.debug('承办部门-社区村:{} 负责人：{} 主管领导：{} '
                 '所属片区：{} 包片领导：{}'.format(index, listChengBanSheQu[index].fuZeRen,
                                                  listChengBanSheQu[index].zhuGuanLingDao,
                                                  listChengBanSheQu[index].suoShuPianQu,
                                                  listChengBanSheQu[index].baoPianLingDao))

for row in range(2, maxRowQuXiTongDaoChu + 1):
    nameCunSearch = sheetQuXiTongDaoChu.cell(row, colChengBanBuMen).value
    nameCunSearchOld = nameCunSearch
    if nameCunSearch is not None and nameCunSearch.strip() != '':
        nameCunSearch = nameCunSearch.strip()
        if nameCunSearch in listChengBanSheQu:
            suoShuPianQu = listChengBanSheQu[nameCunSearch].suoShuPianQu
            baoCunLingDaoName = listChengBanSheQu[nameCunSearch].baoPianLingDao
            duBanLingDaoName = listChengBanSheQu[nameCunSearch].zhuGuanLingDao
            sheetQuXiTongDaoChu.cell(row, 2).value = suoShuPianQu
            sheetQuXiTongDaoChu.cell(row, 3).value = duBanLingDaoName
            if baoCunLingDaoName is not None:
                sheetQuXiTongDaoChu.cell(row, 4).value = baoCunLingDaoName.strip()
            valueCurCellShiFouJieJue = sheetQuXiTongDaoChu.cell(row, colShiFouJieJueAll).value
            valueCurCellShiFouManYi = sheetQuXiTongDaoChu.cell(row, colShiFouManYiAll).value
            valueCurCellShiFouLianXi = sheetQuXiTongDaoChu.cell(row, colShiFouLianXiAll).value
            if valueCurCellShiFouJieJue is not None and valueCurCellShiFouManYi is not None \
                    and valueCurCellShiFouLianXi is not None:
                valueCurCellShiFouJieJue = valueCurCellShiFouJieJue.strip()
                valueCurCellShiFouManYi = valueCurCellShiFouManYi.strip()
                valueCurCellShiFouLianXi = valueCurCellShiFouLianXi.strip()
                if valueCurCellShiFouJieJue == '未解决' or valueCurCellShiFouManYi == '基本满意' \
                        or valueCurCellShiFouManYi == '不满意' or valueCurCellShiFouManYi == '非常不满意':
                    if nameCunSearchOld in listChengBan:
                        listChengBanCount[nameCunSearchOld] = listChengBanCount[nameCunSearchOld] + 1
                    else:
                        listChengBan.append(nameCunSearchOld)
                        listChengBanCount[nameCunSearchOld] = 1
                    baoPianLingDao[nameCunSearchOld] = duBanLingDaoName.strip()
                    baoCunLingDao[nameCunSearchOld] = baoCunLingDaoName.strip()
                    if duBanLingDaoName.strip() not in listBaoPianLingDao:
                        listBaoPianLingDao.append(duBanLingDaoName.strip())
                    if baoCunLingDaoName.strip() not in listBaoCunLingDao:
                        listBaoCunLingDao.append(baoCunLingDaoName.strip())
                    logger.debug('----------------------{} {} {} {}'.format(nameCunSearchOld, valueCurCellShiFouJieJue,
                                                                            valueCurCellShiFouManYi,
                                                                            valueCurCellShiFouLianXi))

logger.debug(sheetQuXiTongDaoChu.max_row)  # 打印表的行数
logger.debug(sheetQuXiTongDaoChu.max_column)  # 打印表的列数
maxRowQuXiTongDaoChu = sheetQuXiTongDaoChu.max_row
maxColQuXiTongDaoChu = sheetQuXiTongDaoChu.max_column

# 合并 是否解决 和 是否满意
numColShiFouJieJue = hanchxl.get_col_index(sheetQuXiTongDaoChu, '是否解决')
sheetQuXiTongDaoChu.insert_cols(numColShiFouJieJue, 1)
sheetQuXiTongDaoChu.cell(1, numColShiFouJieJue).value = '是否解决\n是否满意'

logger.debug('合并 是否解决 和 是否满意')
hanchxl.he_bing_cols(sheetQuXiTongDaoChu, '是否解决', '是否满意', '是否解决\n是否满意')
numColShiFouJieJue = hanchxl.get_col_index(sheetQuXiTongDaoChu, '是否解决')
sheetQuXiTongDaoChu.insert_cols(numColShiFouJieJue, 1)
sheetQuXiTongDaoChu.cell(1, numColShiFouJieJue).value = '是否解决\n是否满意\n是否联系'
hanchxl.he_bing_cols(sheetQuXiTongDaoChu, '是否解决\n是否满意', '是否联系', '是否解决\n是否满意\n是否联系')
numColShiFouJieJue = hanchxl.get_col_index(sheetQuXiTongDaoChu, '是否解决')
sheetQuXiTongDaoChu.insert_cols(numColShiFouJieJue, 1)
sheetQuXiTongDaoChu.cell(1, numColShiFouJieJue).value = '回访结果'
hanchxl.he_bing_cols(sheetQuXiTongDaoChu, '是否解决\n是否满意\n是否联系', '是否见面', '回访结果')

# 删除标题栏多余的文字
# #微信服务号# 12345-2 #APP#  【北京市委书记蔡奇】  #领导留言板#（待解释）  #领导留言板# “12369热线来电” #网络诉求舆情#
# 【北京市市长陈吉宁】 #市政府门户网站互动交流平台# “领导接听”
ColIndexTitle = hanchxl.get_col_index(sheetQuXiTongDaoChu, '标题')
ColIndexContent = hanchxl.get_col_index(sheetQuXiTongDaoChu, '主要内容')
for row in range(2, maxRowQuXiTongDaoChu + 1):
    cellValueTitle = sheetQuXiTongDaoChu.cell(row, ColIndexTitle).value
    cellValueContent = sheetQuXiTongDaoChu.cell(row, ColIndexContent).value
    if cellValueTitle is not None:
        cellValueTitle = cellValueTitle.strip()
        cellValueTitle = cellValueTitle.replace('#微信服务号#', '')
        cellValueTitle = cellValueTitle.replace('12345-2', '')
        cellValueTitle = cellValueTitle.replace('#APP#', '')
        cellValueTitle = cellValueTitle.replace('【北京市委书记尹力】', '')
        cellValueTitle = cellValueTitle.replace('【北京市市长殷勇】', '')
        cellValueTitle = cellValueTitle.replace('#领导留言板#（待解释）', '')
        cellValueTitle = cellValueTitle.replace('#领导留言板#', '')
        cellValueTitle = cellValueTitle.replace('“12369热线来电”', '')
        cellValueTitle = cellValueTitle.replace('“12319热线来电”', '')
        cellValueTitle = cellValueTitle.replace('“12315热线来电”', '')
        cellValueTitle = cellValueTitle.replace('/保密', '')
        cellValueTitle = cellValueTitle.replace('“领导接听”', '')
        cellValueTitle = cellValueTitle.replace('#网络诉求舆情#', '')
        cellValueTitle = cellValueTitle.replace('#市政府门户网站互动交流平台#', '')
        sheetQuXiTongDaoChu.cell(row, ColIndexTitle).value = cellValueTitle
    if cellValueContent is not None:
        cellValueContent = cellValueContent.strip()
        cellValueContent = cellValueContent.replace('工单来源：微信服务号，此工单为网民原文。', '市民反映，')
        cellValueContent = cellValueContent.replace('工单来源：微信服务号，此工单内容为网民原文。', '市民反映，')
        cellValueContent = cellValueContent.replace('注：请及时向来电人反馈办理情况。', '')
        cellValueContent = cellValueContent.replace('注：请及时向来电人反馈办理情况', '')
        cellValueContent = cellValueContent.replace('注：请及时向来电人反馈办理进展。', '')
        cellValueContent = cellValueContent.replace('注：请及时向来电人反馈办理进展', '')
        cellValueContent = cellValueContent.replace('注：请及时向反映人反馈办理情况。', '')
        cellValueContent = cellValueContent.replace('注：请及时向反映人反馈办理情况', '')
        cellValueContent = cellValueContent.replace('注：请\n及时向来电人反馈办理情况。', '')
        cellValueContent = cellValueContent.replace('注：请\n及时向来电人反馈办理情况', '')
        cellValueContent = cellValueContent.replace('注：请及时向来电人反馈事情办理情况', '')
        cellValueContent = cellValueContent.replace('注：请及时向来电人反馈办理信息。', '')

        cellValueContent = cellValueContent.replace('=', '')
        sheetQuXiTongDaoChu.cell(row, ColIndexContent).value = cellValueContent

maxColTitle = sheetQuXiTongDaoChu.max_column
for row_tile_index in range(1, maxColTitle + 1):
    logger.debug('{}'.format(sheetQuXiTongDaoChu.cell(1, row_tile_index).value))

# 按照 承办部门 排序 所有工单明细列表
row_lst = []
for row in sheetQuXiTongDaoChu.rows:
    row_data = [cell.value for cell in row]
    if row_data[1] is not None:
        row_lst.append(row_data)

# 排序
sort_row = hanchxl.sort_row_by_column_name(row_lst, '承办部门')

sort_sheet = bookQuXiTongDaoChu.create_sheet(title='所有工单明细', index=1)
for row, lst in enumerate(sort_row):
    for col, value in enumerate(lst):
        sort_sheet.cell(row + 1, col + 1, value)

bookQuXiTongDaoChu.remove(sheetQuXiTongDaoChu)
sheetQuXiTongDaoChu = sort_sheet

maxColTitle = sheetQuXiTongDaoChu.max_column
sheetShiFenAll = bookQuXiTongDaoChu.create_sheet(title='失分工单明细', index=1)
for row_tile_index in range(1, maxColTitle + 1):
    sheetShiFenAll.cell(1, row_tile_index).value = sheetQuXiTongDaoChu.cell(1, row_tile_index).value

logger.debug('按照 承办部门 排序 所有工单明细列表 完成')

listShiFenLingDao = []
listShiFenLingDaoValue = {}
listShiFenLingDaoCount = {}
listShiFenLingDaoInfo = {}


def ji_suan_shi_fen_inf0(row_insert):
    global listShiFenLingDao
    global listShiFenLingDaoValue
    colPaiDanLeiXing = hanchxl.get_col_index(sheetShiFenAll, '派单类型')
    colShiFouJieJue = hanchxl.get_col_index(sheetShiFenAll, '是否解决')
    colShiFouManYi = hanchxl.get_col_index(sheetShiFenAll, '是否满意')
    colShiFouLianXi = hanchxl.get_col_index(sheetShiFenAll, '是否联系')
    colBaoPian = hanchxl.get_col_index(sheetShiFenAll, '包片领导')
    colResultInfo = hanchxl.get_col_index(sheetShiFenAll, '是否解决\n是否满意')

    valueLeiXing = sheetShiFenAll.cell(row_insert, colPaiDanLeiXing).value
    valueJieJue = sheetShiFenAll.cell(row_insert, colShiFouJieJue).value
    valueManYi = sheetShiFenAll.cell(row_insert, colShiFouManYi).value
    valueLianXi = sheetShiFenAll.cell(row_insert, colShiFouLianXi).value
    valueBaoPian = sheetShiFenAll.cell(row_insert, colBaoPian).value
    valueResultInfo = sheetShiFenAll.cell(row_insert, colResultInfo).value
    if valueLeiXing is not None and valueJieJue is not None and valueManYi is not None and valueLianXi is not None and \
            valueBaoPian is not None and valueResultInfo is not None and numShiPai != 0:
        valueResultInfo = valueResultInfo.replace('\n', '')
        valueDeFen = 0
        valueResult = {}
        if valueLeiXing == '市派':
            valueDeFenBase = round(50 / numShiPai, 5)
        else:
            valueDeFenBase = round(50 / numQuPai, 5)
        if valueLianXi != '联系':
            valueDeFen = round(valueDeFen + valueDeFenBase * 0.1, 2)
        if valueJieJue != '解决':
            valueDeFen = round(valueDeFen + valueDeFenBase * 0.5, 2)
        if valueManYi == '基本满意':
            valueDeFen = round(valueDeFen + valueDeFenBase * 0.4 * 0.2, 2)
        if valueManYi == '不满意' or valueManYi == '非常不满意':
            valueDeFen = round(valueDeFen + valueDeFenBase * 0.4, 2)
        if valueBaoPian in listShiFenLingDao:
            listShiFenLingDaoValue[valueBaoPian] = listShiFenLingDaoValue[valueBaoPian] + valueDeFen
            listShiFenLingDaoCount[valueBaoPian] = listShiFenLingDaoCount[valueBaoPian] + 1
            if valueResultInfo in list(listShiFenLingDaoInfo[valueBaoPian].keys()):
                listShiFenLingDaoInfo[valueBaoPian][valueResultInfo] = listShiFenLingDaoInfo[valueBaoPian][
                                                                           valueResultInfo] + 1
            else:
                listShiFenLingDaoInfo[valueBaoPian][valueResultInfo] = 1
        else:
            listShiFenLingDao.append(valueBaoPian)
            listShiFenLingDaoValue[valueBaoPian] = valueDeFen
            listShiFenLingDaoCount[valueBaoPian] = 1
            valueResult[valueResultInfo] = 1
            listShiFenLingDaoInfo[valueBaoPian] = valueResult
            logger.debug('{}'.format(listShiFenLingDaoInfo))
        logger.debug(
            '{}  失分 {} 分  累计失分 {} 分'.format(valueBaoPian, valueDeFen, listShiFenLingDaoValue[valueBaoPian]))


def inset_row_to_new_sheet(row_insert):
    rowMaxWeiJieJue = sheetShiFenAll.max_row
    maxColTitleInsert = sheetQuXiTongDaoChu.max_column
    colChengBanBuMen = hanchxl.get_col_index(sheetQuXiTongDaoChu, '承办部门')
    valueInsertChengBan = sheetQuXiTongDaoChu.cell(row_insert, colChengBanBuMen).value
    if valueInsertChengBan.find('综合城管') != -1:
        valueInsertChengBan = '综合行政执法队'
    if valueInsertChengBan.find('流动人口管理组') != -1:
        valueInsertChengBan = '社会治理组'
    if valueInsertChengBan is not None:
        valueInsertChengBan = valueInsertChengBan.strip()
        for row_ins in range(2, rowMaxWeiJieJue + 1):
            valueCellInsert = sheetShiFenAll.cell(row_ins, colChengBanBuMen).value
            if valueCellInsert is not None:
                valueCellInsert = valueCellInsert.strip()
                if valueCellInsert.find('综合城管') != -1:
                    valueCellInsert = '综合行政执法队'
                if valueCellInsert.find('流动人口管理组') != -1:
                    valueCellInsert = '社会治理组'
                if valueCellInsert == valueInsertChengBan:
                    sheetShiFenAll.insert_rows(row_ins + 1)
                    for col_title in range(1, maxColTitleInsert + 1):
                        ss = sheetQuXiTongDaoChu.cell(row_insert, col_title).value
                        sheetShiFenAll.cell(row_ins + 1, col_title).value = ss
                    ji_suan_shi_fen_inf0(row_ins + 1)
                    return
        sheetShiFenAll.insert_rows(rowMaxWeiJieJue + 1)
        for col_title in range(1, maxColTitleInsert + 1):
            sst = sheetQuXiTongDaoChu.cell(row_insert, col_title).value
            sheetShiFenAll.cell(rowMaxWeiJieJue + 1, col_title).value = sst
        ji_suan_shi_fen_inf0(rowMaxWeiJieJue + 1)


def inset_pian_qu_to_new_sheet(suo_shu_pian_qu):
    maxRowQu = sheetQuXiTongDaoChu.max_row
    colShiFouJieJue = hanchxl.get_col_index(sheetQuXiTongDaoChu, '是否解决')
    colShiFouManYi = hanchxl.get_col_index(sheetQuXiTongDaoChu, '是否满意')
    colShiFouLianXi = hanchxl.get_col_index(sheetQuXiTongDaoChu, '是否联系')
    colPianQu = hanchxl.get_col_index(sheetQuXiTongDaoChu, '片区')
    for row_index in range(2, maxRowQu + 1):
        valueCurCellShiFouJieJueInsert = sheetQuXiTongDaoChu.cell(row_index, colShiFouJieJue).value
        valueCurCellShiFouManYiInsert = sheetQuXiTongDaoChu.cell(row_index, colShiFouManYi).value
        valueCurCellShiFouLianXiInsert = sheetQuXiTongDaoChu.cell(row_index, colShiFouLianXi).value
        if valueCurCellShiFouJieJueInsert is None or valueCurCellShiFouManYiInsert is None \
                or valueCurCellShiFouLianXiInsert is None:
            continue
        valueCurCellShiFouJieJueInsert = valueCurCellShiFouJieJueInsert.strip()
        valueCurCellShiFouManYiInsert = valueCurCellShiFouManYiInsert.strip()
        valueCurCellShiFouLianXiInsert = valueCurCellShiFouLianXiInsert.strip()
        if valueCurCellShiFouJieJueInsert == '未解决' or valueCurCellShiFouManYiInsert == '基本满意' \
                or valueCurCellShiFouManYiInsert == '不满意' or valueCurCellShiFouManYiInsert == '非常不满意':
            valueCurCell = sheetQuXiTongDaoChu.cell(row_index, colPianQu).value
            if valueCurCell is not None:
                valueCurCell = valueCurCell.strip().replace(' ', '')
                if valueCurCell == suo_shu_pian_qu:
                    # logger.debug('插入 ： {} 行 '.format(row_index))
                    inset_row_to_new_sheet(row_index)
            elif suo_shu_pian_qu == '':
                inset_row_to_new_sheet(row_index)


inset_pian_qu_to_new_sheet('科室')
inset_pian_qu_to_new_sheet('中部片区')
inset_pian_qu_to_new_sheet('北部片区')
inset_pian_qu_to_new_sheet('西南片区')
inset_pian_qu_to_new_sheet('东部片区')
inset_pian_qu_to_new_sheet('')

logger.debug('listShiFenLingDao:{} '.format(listShiFenLingDao))
logger.debug('listShiFenLingDaoValue:{} '.format(listShiFenLingDaoValue))
logger.debug('listShiFenLingDaoCount:{} '.format(listShiFenLingDaoCount))
logger.debug('listShiFenLingDaoInfo:{} '.format(listShiFenLingDaoInfo))

numShiFenCount = 0
for lingdaoname in listShiFenLingDaoValue:
    listShiFenLingDaoValue[lingdaoname] = round(listShiFenLingDaoValue[lingdaoname], 2)
    numShiFenCount = numShiFenCount + listShiFenLingDaoValue[lingdaoname]

logger.debug('listShiFenLingDao:{} '.format(listShiFenLingDao))
logger.debug('numShiFenCount: {}  listShiFenLingDaoValue:{} '.format(numShiFenCount, listShiFenLingDaoValue))


def delete_col_title(sheet_opt, delete_title):
    dele_col_index = hanchxl.get_col_index(sheet_opt, delete_title)
    sheet_opt.delete_cols(dele_col_index)


delete_col_title(sheetShiFenAll, '是否解决\n是否满意\n是否联系')
delete_col_title(sheetShiFenAll, '是否解决\n是否满意')
delete_col_title(sheetShiFenAll, '是否解决')
delete_col_title(sheetShiFenAll, '是否满意')
delete_col_title(sheetShiFenAll, '是否联系')
delete_col_title(sheetShiFenAll, '是否见面')
delete_col_title(sheetShiFenAll, '回访状态')

# 修改参与部门
hanchxl.xiu_gai_cheng_ban_bu_men(sheetShiFenAll, '参与部门', '应急小分队', '市民诉求处置中心')
hanchxl.xiu_gai_cheng_ban_bu_men(sheetShiFenAll, '参与部门', '疑难工单', '市民诉求处置中心')
hanchxl.xiu_gai_cheng_ban_bu_men(sheetShiFenAll, '参与部门', '疑难案件', '市民诉求处置中心')
hanchxl.xiu_gai_cheng_ban_bu_men(sheetShiFenAll, '参与部门', '智汇雅苑诺亚方舟物业', '智汇雅苑社区')

# 修改参与部门中 相同的承办部门
maxWeiJieRow = sheetShiFenAll.max_row
colCanYu = hanchxl.get_col_index(sheetShiFenAll, '参与部门')
colChengBan = hanchxl.get_col_index(sheetShiFenAll, '承办部门')
for rowCanYu in range(2, maxWeiJieRow + 1):
    valueCellCanYu = sheetShiFenAll.cell(rowCanYu, colCanYu).value
    valueCellChengBan = sheetShiFenAll.cell(rowCanYu, colChengBan).value
    valueCellCanYu = valueCellCanYu.strip()
    valueCellCanYu = valueCellCanYu.replace(valueCellChengBan + ',', '')
    valueCellCanYu = valueCellCanYu.replace(',' + valueCellChengBan, '')
    valueCellCanYu = valueCellCanYu.replace(valueCellChengBan, '')
    sheetShiFenAll.cell(rowCanYu, colCanYu).value = valueCellCanYu

listLingDaoInFo = chengBan.get_list_ling_dao()
logger.debug('领导顺序：{}'.format(listLingDaoInFo))
listShunXu = list(listLingDaoInFo.keys())
# listShunXu = listLingDaoInFo.keys()
# listShunXu = listLingDaoInFo.keys()
logger.debug('领导顺序：{}'.format(listShunXu))
# listShunXu = ['杨海峰', '路  鹏', '杨  琳', '邱珍山', '肖英刚', '宋宏生', '彭  超', '张新宝', '许艳春', '惠  妍', '李占奇',
#               '栗景鸿', '穆甫元', '曹玉清', '李  岩', '孙多前', '赵  龙', '谢  然', '郑海涛']

sheetShiFenCopy = bookQuXiTongDaoChu.copy_worksheet(sheetShiFenAll)
sheetShiFenAll = bookQuXiTongDaoChu.create_sheet(title='主管领导失分工单明细', index=2)
listLingDaoShiFenTitle = ['序号', '主管领导', '承办部门', '工单编号', '标题', '主要内容', '来电人-合并', '办理结果',
                          '回访结果', '截止剩余时间']
listIndexShiResult = {}

for col in range(0, len(listLingDaoShiFenTitle)):
    sheetShiFenAll.cell(1, col + 1).value = listLingDaoShiFenTitle[col]
    listIndexShiResult[listLingDaoShiFenTitle[col]] = col + 1

listIndexShiCopy = {}
listIndexShiCopy['片区'] = hanchxl.get_col_index(sheetShiFenCopy, '片区')
listIndexShiCopy['包片领导'] = hanchxl.get_col_index(sheetShiFenCopy, '包片领导')
for col in range(2, len(listLingDaoShiFenTitle)):
    listIndexShiCopy[listLingDaoShiFenTitle[col]] = hanchxl.get_col_index(sheetShiFenCopy, listLingDaoShiFenTitle[col])


def insert_into_lingDao(sheet_src, sheet_dest, name_ling_dao, is_ke_shi):
    maxRowSrc = sheet_src.max_row
    for col in range(2, maxRowSrc + 1):
        maxRowDest = sheet_dest.max_row
        isInsert = False
        valueLingDao = sheet_src.cell(col, listIndexShiCopy['包片领导']).value
        valuePianQu = sheet_src.cell(col, listIndexShiCopy['片区']).value
        if valuePianQu is not None and valueLingDao is not None:
            valueLingDao_r = valueLingDao.replace(' ', '')
            name_ling_dao_r = name_ling_dao.replace(' ', '')
            if valueLingDao_r == name_ling_dao_r and is_ke_shi is True and valuePianQu == '科室':
                isInsert = True
            if valueLingDao_r == name_ling_dao_r and is_ke_shi is False and valuePianQu != '科室':
                isInsert = True
            if isInsert is True:
                sheet_dest.cell(maxRowDest + 1, 1).value = maxRowDest
                sheet_dest.cell(maxRowDest + 1, listIndexShiResult['主管领导']).value = name_ling_dao
                for index in range(2, len(listLingDaoShiFenTitle)):
                    sheet_dest.cell(maxRowDest + 1, listIndexShiResult[listLingDaoShiFenTitle[index]]).value = \
                        sheet_src.cell(col, listIndexShiCopy[listLingDaoShiFenTitle[index]]).value


for rowShunXu in range(0, len(listShunXu)):
    if listShunXu[rowShunXu] in listBaoCunLingDao:
        insert_into_lingDao(sheetShiFenCopy, sheetShiFenAll, listShunXu[rowShunXu], True)
        insert_into_lingDao(sheetShiFenCopy, sheetShiFenAll, listShunXu[rowShunXu], False)

bookQuXiTongDaoChu.remove(sheetShiFenCopy)
bookQuXiTongDaoChu.save(fileResultSave)
#
# fileResultContent = ''
# fileMustDo = dirPath + '\\每日必办文案.txt'
# if isProcessReceiving is True:
#     logger.debug('-全部工单.xlsx-')
#     logger.debug('接单量文件：{}'.format(fileGongDanQuanBu))
#     fileResultContent = fileResultContent + "\n今日工单情况"
#     bookTodayMustDo = openpyxl.load_workbook(fileGongDanQuanBu, data_only=True)
#     sheetTodayMustDo = bookTodayMustDo.active
#     maxRowTodayMustDo = sheetTodayMustDo.max_row
#     maxColTodayMustDo = sheetTodayMustDo.max_column
#     logger.debug('行数：{}  列数：{}'.format(maxRowTodayMustDo, maxColTodayMustDo))  # 打印表的行列数
#
#     # 修改承办部门
#     hanchxl.xiu_gai_cheng_ban_bu_men(sheetTodayMustDo, '承办部门', '疑难案件', '市民诉求处置中心')
#     hanchxl.xiu_gai_cheng_ban_bu_men(sheetTodayMustDo, '承办部门', '疑难工单', '市民诉求处置中心')
#     hanchxl.xiu_gai_cheng_ban_bu_men(sheetTodayMustDo, '承办部门', '应急小分队', '市民诉求处置中心')
#     hanchxl.xiu_gai_cheng_ban_bu_men(sheetTodayMustDo, '承办部门', '流动人口管理组', '社会治理组')
#     hanchxl.xiu_gai_cheng_ban_bu_men(sheetTodayMustDo, '承办部门', '智汇雅苑诺亚方舟物业', '智汇雅苑社区')
#
#     # 计算 双满工单数量 解决基本满意数量 解决不满意数量
#     colIndexCodeId = hanchxl.get_col_index(sheetTodayMustDo, '工单编号')
#     sheetTodayMustDo.insert_cols(colIndexCodeId, 1)
#     sheetTodayMustDo.cell(1, colIndexCodeId).value = '是否今日工单'
#
#     colIndexIsToday = hanchxl.get_col_index(sheetTodayMustDo, '是否今日工单')
#     colIndexCodeId = hanchxl.get_col_index(sheetTodayMustDo, '工单编号')
#     colIndexType = hanchxl.get_col_index(sheetTodayMustDo, '工单类型(直派/转派)')
#     colIndexChengBan = hanchxl.get_col_index(sheetTodayMustDo, '承办部门')
#     colIndexJieDan = hanchxl.get_col_index(sheetTodayMustDo, '接单部门')
#     colIndexBiaoQian = hanchxl.get_col_index(sheetTodayMustDo, '标签')
#     colIndexZhiPai = hanchxl.get_col_index(sheetTodayMustDo, '指派时间')
#
#     # 社区（村）
#     numSheQuCun = 0
#     keyListSheQuCun = []
#     tinyDictSheQuCun = {}
#     # 拱辰街道机关科室
#     numJiGuanKeShi = 0
#     keyListJiGuanKeShi = []
#     tinyDictJiGuanKeShi = {}
#     logger.debug('承办部门：{}    接单部门：{}'.format(colIndexChengBan, colIndexJieDan))
#
#     timeYesterdayStr = datetime.datetime.fromtimestamp(timeCur.timestamp()-60*60*24).strftime('%Y-%m-%d')
#     timeYesterdayStr = timeYesterdayStr + '  09:00:00'
#     timeTodayStr = '{}-{}-{} 09:00:00'.format(timeCur.year, timeCur.month, timeCur.day)
#
#     logger.debug('昨天：{}    今天：{}'.format(timeYesterdayStr, timeTodayStr))
#     timeYesterdayArray = time.strptime(timeYesterdayStr, '%Y-%m-%d %H:%M:%S')
#     timeTodayArray = time.strptime(timeTodayStr, '%Y-%m-%d %H:%M:%S')
#     timeYesterday = int(time.mktime(timeYesterdayArray))
#     timeToday = int(time.mktime(timeTodayArray))
#     logger.debug('昨天：{}    今天：{}'.format(timeYesterday, timeToday))
#
#     codeIdArray = []
#     codeIdArrayNoPai = []
#     numShiPai = 0
#     numNoPai = 0
#     for rowSource in range(2, maxRowTodayMustDo + 1):
#         zhi_pai_shi_jian_excel = sheetTodayMustDo.cell(rowSource, colIndexZhiPai).value
#         # int(time.mktime(zhi_pai_shi_jian_excel.timetuple()))
#         time_zhi_pai_excel_time_stamp = int(time.mktime(time.strptime(zhi_pai_shi_jian_excel, '%Y-%m-%d %H:%M:%S')))
#         if time_zhi_pai_excel_time_stamp >= timeToday or time_zhi_pai_excel_time_stamp < timeYesterday:
#             sheetTodayMustDo.cell(rowSource, colIndexIsToday).value = 0
#             continue
#         cellCodeId = sheetTodayMustDo.cell(rowSource, colIndexCodeId).value
#         cellType = sheetTodayMustDo.cell(rowSource, colIndexType).value
#         cellJieDan = sheetTodayMustDo.cell(rowSource, colIndexJieDan).value
#         cellChengBan = sheetTodayMustDo.cell(rowSource, colIndexChengBan).value
#         colBiaoQian = sheetTodayMustDo.cell(rowSource, colIndexBiaoQian).value
#         colBiaoQian = '{}'.format(colBiaoQian)
#         if cellChengBan is None or cellJieDan is None:
#             sheetTodayMustDo.cell(rowSource, colIndexIsToday).value = 2
#             numNoPai = numNoPai + 1
#             codeIdArrayNoPai.append(cellCodeId)
#             continue
#         sheetTodayMustDo.cell(rowSource, colIndexIsToday).value = 1
#         codeIdArray.append(cellCodeId)
#         if cellType == '直派':
#             numShiPai = numShiPai + 1
#         if cellChengBan.find('综合城管') != -1:
#             cellChengBan = '综合行政执法队'
#         if cellChengBan.find('流动人口管理组') != -1:
#             cellChengBan = '社会治理组'
#         if cellJieDan is not None:
#             cellJieDan = cellJieDan.strip()
#             if cellChengBan is not None:
#                 cellChengBan = cellChengBan.strip()
#                 logger.debug('承办部门：{} '.format(cellChengBan))
#                 if cellJieDan == '拱辰街道机关科室':
#                     numJiGuanKeShi = numJiGuanKeShi + 1
#                     if cellChengBan in keyListJiGuanKeShi:
#                         tinyDictJiGuanKeShi[cellChengBan] = tinyDictJiGuanKeShi[cellChengBan] + 1
#                     else:
#                         keyListJiGuanKeShi.append(cellChengBan)
#                         tinyDictJiGuanKeShi[cellChengBan] = 1
#                 else:
#                     if cellJieDan == '社区（村）':
#                         numSheQuCun = numSheQuCun + 1
#                         if cellChengBan in keyListSheQuCun:
#                             tinyDictSheQuCun[cellChengBan] = tinyDictSheQuCun[cellChengBan] + 1
#                         else:
#                             keyListSheQuCun.append(cellChengBan)
#                             tinyDictSheQuCun[cellChengBan] = 1
#     logger.debug('打印今日派单 开始 接单数量：{}'.format(len(codeIdArray)))
#     for codeIdArrayItem in codeIdArray:
#         logger.debug('{}'.format(codeIdArrayItem))
#     logger.debug('打印今日派单 结束')
#     numAllJieDan = numJiGuanKeShi + numSheQuCun
#     logger.debug('{} 件工单尚未分配承办部门'.format(numNoPai))
#     if numNoPai > 0:
#         fileResultContent = fileResultContent + '\n注意：\n当前尚未分配承办部门工单 {} 件工单：\n'.format(numNoPai)
#         for codeIdNoPai in codeIdArrayNoPai:
#             fileResultContent = fileResultContent + '{} \n'.format(codeIdNoPai)
#     fileResultContent = fileResultContent + "\n{}月{}日9时".format(datatimeYesterday.month, datatimeYesterday.day)
#     fileResultContent = fileResultContent + '至{}月{}日9时，'.format(timeCur.month, timeCur.day)
#     if numAllJieDan > 0:
#         fileResultContent = fileResultContent + '共接收工单{}件'.format(numAllJieDan)
#         fileResultContent = fileResultContent + '（区派{}件，市派{}件，详见附件1）'.format(numAllJieDan-numShiPai, numShiPai)
#         fileResultContent = fileResultContent + '，比前一日增加/减少X件，均为日常诉求。\n'
#         if numJiGuanKeShi > 0:
#             fileResultContent = fileResultContent + '涉及机关科室{}件（'.format(numJiGuanKeShi)
#             tinyDictJiGuanKeShi = sorted(tinyDictJiGuanKeShi.items(), key=lambda kv: kv[1], reverse=True)
#             logger.debug('{}'.format(tinyDictJiGuanKeShi))
#             numLast = 0
#             numCount = 0
#             numIndex = 0
#             for keyNoKeShi in tinyDictJiGuanKeShi:
#                 numIndex = numIndex + 1
#                 if numLast == 0:
#                     numLast = keyNoKeShi[1]
#                 else:
#                     if numLast != keyNoKeShi[1]:
#                         if numCount > 1:
#                             fileResultContent = fileResultContent + '各{}件，'.format(numLast)
#                         else:
#                             fileResultContent = fileResultContent + '{}件，'.format(numLast)
#                         numCount = 0
#                 if numLast == keyNoKeShi[1]:
#                     if numCount == 0:
#                         fileResultContent = fileResultContent + '{}'.format(keyNoKeShi[0])
#                     else:
#                         fileResultContent = fileResultContent + '、{}'.format(keyNoKeShi[0])
#                 else:
#                     fileResultContent = fileResultContent + '{}'.format(keyNoKeShi[0])
#                 numCount = numCount + 1
#                 numLast = keyNoKeShi[1]
#             if numCount > 1:
#                 fileResultContent = fileResultContent + '各{}件'.format(numLast)
#             else:
#                 fileResultContent = fileResultContent + '{}件'.format(numLast)
#             fileResultContent = fileResultContent + '）。'
#
#         if numSheQuCun > 0:
#             fileResultContent = fileResultContent + '\n涉及社区（村）{}件（'.format(numSheQuCun)
#             tinyDictSheQuCun = sorted(tinyDictSheQuCun.items(), key=lambda kv: kv[1], reverse=True)
#             # logger.debug('{}'.format(tinyDictSheQuCun))
#             numLastSheQu = 0
#             numIndexSheQu = 0
#             numCountSheQu = 0
#             for keyNoSheQu in tinyDictSheQuCun:
#                 numIndexSheQu = numIndexSheQu + 1
#                 if numLastSheQu == 0:
#                     numLastSheQu = keyNoSheQu[1]
#                 else:
#                     if numLastSheQu != keyNoSheQu[1]:
#                         if numCountSheQu > 1:
#                             fileResultContent = fileResultContent + '各{}件，'.format(numLastSheQu)
#                         else:
#                             fileResultContent = fileResultContent + '{}件，'.format(numLastSheQu)
#                         numCountSheQu = 0
#                 if numLastSheQu == keyNoSheQu[1]:
#                     if numCountSheQu == 0:
#                         fileResultContent = fileResultContent + '{}'.format(keyNoSheQu[0])
#                     else:
#                         fileResultContent = fileResultContent + '、{}'.format(keyNoSheQu[0])
#                 else:
#                     fileResultContent = fileResultContent + '{}'.format(keyNoSheQu[0])
#                 numCountSheQu = numCountSheQu + 1
#                 numLastSheQu = keyNoSheQu[1]
#             if numCountSheQu > 1:
#                 fileResultContent = fileResultContent + '各{}件'.format(numLastSheQu)
#             else:
#                 fileResultContent = fileResultContent + '{}件'.format(numLastSheQu)
#             fileResultContent = fileResultContent + '）。'
#         fileResultContent = fileResultContent + '截止16时，已办结X件，办理中X件（详见附件2）。'
#     logger.debug('fileResultSave:{}'.format(fileMustDo))
#     logger.debug('今日工单情况 :{}'.format(fileResultContent))
#     bookTodayMustDo.save(fileGongDanToday)
#     # with open(fileMustDo, "w") as f:
#     #     f.write(fileResultContent)
#
# logger.debug('-每日必办文案-工单回访情况.xlsx-')
# # 打开今日未解决工单明细
# if isProcessUnresolved is True:
#     # bookWeiJieJue = openpyxl.load_workbook(fileResultSave, data_only=True)
#     # sheetShiFenAll = bookWeiJieJue['所有工单明细']
#     sheetShiFenAll = bookQuXiTongDaoChu.active
#     maxRowWeiJieJue = sheetShiFenAll.max_row
#     maxColWeiJieJue = sheetShiFenAll.max_column
#     logger.debug('行数：{}  列数：{}'.format(maxRowWeiJieJue, maxColWeiJieJue))  # 打印表的行列数
#
#     # 获取有效回访件数
#     colIndex = hanchxl.get_col_index(sheetShiFenAll, '回访状态')
#     maxRowSource = sheetShiFenAll.max_row
#     numYouXiao = 0
#     for rowSource in range(2, maxRowSource + 1):
#         cellValue = sheetShiFenAll.cell(rowSource, colIndex).value
#         if cellValue is not None:
#             cellValue = cellValue.strip()
#             if cellValue == '有效回访':
#                 numYouXiao = numYouXiao + 1
#
#     # 计算 双满工单数量 解决基本满意数量 解决不满意数量
#     colIndexJieJueManYi = hanchxl.get_col_index(sheetShiFenAll, '是否解决\n是否满意')
#     colIndexChengBan = hanchxl.get_col_index(sheetShiFenAll, '承办部门')
#     colIndexTitle = hanchxl.get_col_index(sheetShiFenAll, '标题')
#     colIndexArea = hanchxl.get_col_index(sheetShiFenAll, '片区')
#
#     # 解决 满意
#     numDoubleOk = 0
#     # 解决 基本满意
#     numJiBenManYi = 0
#     keyListJiBenManYi = []
#     tinyDictJiBenManYiNum = {}
#     tinyDictJiBenManYiTitle = {}
#     # 解决 不满意
#     numJieJueBuManYi = 0
#     keyListBuManYi = []
#     tinyDictBuManYiNum = {}
#     tinyDictBuManYiTitle = {}
#     # 未解决 科室
#     numNoKeShi = 0
#     keyListNoKeShi = []
#     dictNoKeShiNum = {}
#     # 未解决 社区/村
#     numNoSheQu = 0
#     keyListNoSheQu = []
#     dictNoSheQuNum = {}
#
#     for rowSource in range(2, maxRowSource + 1):
#         cellValue = sheetShiFenAll.cell(rowSource, colIndexJieJueManYi).value
#         cellChengBan = sheetShiFenAll.cell(rowSource, colIndexChengBan).value
#         cellValueYouXiao = sheetShiFenAll.cell(rowSource, colIndex).value
#         if cellValueYouXiao != '有效回访':
#             continue
#         if cellChengBan is not None:
#             if cellChengBan.find('综合城管') != -1:
#                 cellChengBan = '综合行政执法队'
#             if cellChengBan.find('流动人口管理组') != -1:
#                 cellChengBan = '社会治理组'
#             cellArea = sheetShiFenAll.cell(rowSource, colIndexArea).value
#             cellTitle = sheetShiFenAll.cell(rowSource, colIndexTitle).value
#             if cellValue is not None:
#                 cellValue = cellValue.strip()
#                 if cellChengBan is not None:
#                     cellChengBan = cellChengBan.strip()
#                     if cellTitle is not None:
#                         cellTitle = cellTitle.strip()
#                         # logger.debug('{}'.format(cellValue))
#                         if cellValue == '解决\n满意' or cellValue == '解决\n非常满意':
#                             numDoubleOk = numDoubleOk + 1
#                             # logger.debug('{}-------true'.format(cellValue))
#                             continue
#                         if cellValue == '解决\n基本满意':
#                             numJiBenManYi = numJiBenManYi + 1
#                             if cellChengBan in keyListJiBenManYi:
#                                 tinyDictJiBenManYiNum[cellChengBan] = tinyDictJiBenManYiNum[cellChengBan] + 1
#                                 cellTitleOld = tinyDictJiBenManYiTitle[cellChengBan]
#                                 tinyDictJiBenManYiTitle[cellChengBan] = cellTitleOld + '、' + cellTitle
#                             else:
#                                 keyListJiBenManYi.append(cellChengBan)
#                                 tinyDictJiBenManYiNum[cellChengBan] = 1
#                                 tinyDictJiBenManYiTitle[cellChengBan] = cellTitle
#
#                         if cellValue == '解决\n不满意' or cellValue == '解决\n非常不满意':
#                             numJieJueBuManYi = numJieJueBuManYi + 1
#                             if cellChengBan in keyListBuManYi:
#                                 tinyDictBuManYiNum[cellChengBan] = tinyDictBuManYiNum[cellChengBan] + 1
#                                 cellTitleOld = tinyDictBuManYiTitle[cellChengBan]
#                                 tinyDictBuManYiTitle[cellChengBan] = cellTitleOld + '、' + cellTitle
#                             else:
#                                 keyListBuManYi.append(cellChengBan)
#                                 tinyDictBuManYiNum[cellChengBan] = 1
#                                 tinyDictBuManYiTitle[cellChengBan] = cellTitle
#
#                         # logger.debug('-{}-'.format(cellChengBan))
#                         if cellArea is not None:
#                             cellArea = cellArea.strip()
#                             cellArea = cellArea.replace(' ', '')
#                             if cellArea == '科室':
#                                 numNoKeShi = numNoKeShi + 1
#                                 if cellChengBan in keyListNoKeShi:
#                                     dictNoKeShiNum[cellChengBan] = dictNoKeShiNum[cellChengBan] + 1
#                                 else:
#                                     keyListNoKeShi.append(cellChengBan)
#                                     dictNoKeShiNum[cellChengBan] = 1
#                                 logger.debug('numNoKeShi：{} {}：{}'.format(numNoKeShi, cellChengBan,
#                                                                           dictNoKeShiNum[cellChengBan]))
#                             else:
#                                 numNoSheQu = numNoSheQu + 1
#                                 if cellChengBan in keyListNoSheQu:
#                                     dictNoSheQuNum[cellChengBan] = dictNoSheQuNum[cellChengBan] + 1
#                                 else:
#                                     keyListNoSheQu.append(cellChengBan)
#                                     dictNoSheQuNum[cellChengBan] = 1
#
#
#
#     for cellChengBan in keyListJiBenManYi:
#         if tinyDictJiBenManYiNum[cellChengBan] > 1:
#             tinyDictJiBenManYiTitle[cellChengBan] = tinyDictJiBenManYiTitle[cellChengBan] + '各1件'
#     for cellChengBan in keyListBuManYi:
#         if tinyDictBuManYiNum[cellChengBan] > 1:
#             tinyDictBuManYiTitle[cellChengBan] = tinyDictBuManYiTitle[cellChengBan] + '各1件'
#
#     # fileResultContent = fileResultContent + '\r\n工单回访情况\r\n'
#     # logger.debug('区级有效回访工单{}件，'.format(numYouXiao))
#     # logger.debug('其中双满工单{}件；'.format(numDoubleOk))
#     # logger.debug('解决基本满意工单{}件'.format(numJiBenManYi))
#     # logger.debug('解决基本满意工单{}件'.format(numJieJueBuManYi))
#     # logger.debug('未解决工单{}件'.format(numNoKeShi + numNoSheQu))
#
#     fileResultContent = fileResultContent + '\r\n工单回访情况-新版\r\n'
#     fileResultContent = fileResultContent + "{}月{}日9时".format(datatimeYesterday.month, datatimeYesterday.day)
#     fileResultContent = fileResultContent + '至{}月{}日9时，'.format(timeCur.month, timeCur.day)
#     fileResultContent = fileResultContent + '区级有效回访工单{}件，'.format(numYouXiao)
#     fileResultContent = fileResultContent + '其中双满工单{}件,'.format(numDoubleOk, numYouXiao-numDoubleOk)
#     fileResultContent = fileResultContent + '未解决工单{}件，'.format(numNoKeShi + numNoSheQu - numJiBenManYi - numJieJueBuManYi)
#     fileResultContent = fileResultContent + '解决基本满意工单{}件，'.format(numJiBenManYi)
#     fileResultContent = fileResultContent + '解决不满意工单{}件，'.format(numJieJueBuManYi)
#     fileResultContent = fileResultContent + '共计失分工单{}件,'.format(numYouXiao-numDoubleOk)
#     fileResultContent = fileResultContent + '其中需提级办理X件。详见附件4。'.format(numYouXiao - numDoubleOk)
#     fileResultContent = fileResultContent + '\r\n工单回访情况\r\n'
#     numWuCha = numYouXiao - (numDoubleOk + numNoKeShi + numNoSheQu)
#     if numWuCha != 0:
#         fileResultContent = fileResultContent + '\r\n请注意：有{}工单未匹配到承办部门，请仔细核对！！！！！\r\n\r\n'.format(numWuCha)
#         fileResultContent = fileResultContent + '区级有效回访工单{}件，'.format(numYouXiao)
#         fileResultContent = fileResultContent + '\r\n其中双满工单{}件；'.format(numDoubleOk)
#         fileResultContent = fileResultContent + '\r\n解决基本满意工单{}件'.format(numJiBenManYi)
#         fileResultContent = fileResultContent + '\r\n解决不满意工单{}件'.format(numJieJueBuManYi)
#         fileResultContent = fileResultContent + '\r\n未解决工单{}件\r\n'.format(numNoKeShi + numNoSheQu)
#
#     fileResultContent = fileResultContent + "{}月{}日9时".format(datatimeYesterday.month, datatimeYesterday.day)
#     fileResultContent = fileResultContent + '至{}月{}日9时，'.format(timeCur.month, timeCur.day)
#     fileResultContent = fileResultContent + '区级有效回访工单{}件，'.format(numYouXiao)
#     fileResultContent = fileResultContent + '其中双满工单{}件,失分工单{}件（'.format(numDoubleOk, numYouXiao-numDoubleOk)
#     fileResultContent = fileResultContent + '未解决工单{}件，'.format(numNoKeShi + numNoSheQu - numJiBenManYi - numJieJueBuManYi)
#     if numJiBenManYi > 0:
#         fileResultContent = fileResultContent + '解决基本满意工单{}件（'.format(numJiBenManYi)
#         tinyDictJiBenManYiNum = sorted(tinyDictJiBenManYiNum.items(), key=lambda kv: kv[1], reverse=True)
#         logger.debug('解决基本满意工单{}件：{}'.format(numJiBenManYi, tinyDictJiBenManYiNum))
#         for key in tinyDictJiBenManYiNum:
#             value = '{}{}件：{}'.format(key[0], key[1], tinyDictJiBenManYiTitle[key[0]])
#             if key != tinyDictJiBenManYiNum[0]:
#                 value = '；' + value
#             fileResultContent = fileResultContent + value
#             # logger.debug('{}'.format(value))
#         fileResultContent = fileResultContent + '）。'
#
#     if numJieJueBuManYi > 0:
#         fileResultContent = fileResultContent + '解决不满意工单{}件（'.format(numJieJueBuManYi)
#         tinyDictBuManYiNum = sorted(tinyDictBuManYiNum.items(), key=lambda kv: kv[1], reverse=True)
#         logger.debug('解决不满意工单{}件：{}'.format(numJieJueBuManYi, tinyDictBuManYiNum))
#         for keyBuManYi in tinyDictBuManYiNum:
#             value = '{}{}件：{}；'.format(keyBuManYi[0], keyBuManYi[1], tinyDictBuManYiTitle[keyBuManYi[0]])
#             fileResultContent = fileResultContent + value
#             # logger.debug('{}'.format(value))
#         fileResultContent = fileResultContent + '）。'
#
#     if numNoKeShi + numNoSheQu > 0:
#         fileResultContent = fileResultContent + '\n失分工单{}件，'.format(numYouXiao-numDoubleOk)
#         fileResultContent = fileResultContent + '经督办申请二次回访0件，剩余失分工单{}件：'.format(numYouXiao-numDoubleOk)
#         if numNoKeShi > 0:
#             fileResultContent = fileResultContent + '其中涉及机关科室{}件（'.format(numNoKeShi, numNoKeShi)
#             dictNoKeShiNum = sorted(dictNoKeShiNum.items(), key=lambda kv: kv[1], reverse=True)
#             # logger.debug('{}'.format(dictNoKeShiNum))
#             numLast = 0
#             numCount = 0
#             numIndex = 0
#             for keyNoKeShi in dictNoKeShiNum:
#                 numIndex = numIndex + 1
#                 if numLast == 0:
#                     numLast = keyNoKeShi[1]
#                 else:
#                     if numLast != keyNoKeShi[1]:
#                         if numCount > 1:
#                             fileResultContent = fileResultContent + '各{}件，'.format(numLast)
#                         else:
#                             fileResultContent = fileResultContent + '{}件，'.format(numLast)
#                         numCount = 0
#                 if numLast == keyNoKeShi[1]:
#                     if numCount == 0:
#                         fileResultContent = fileResultContent + '{}'.format(keyNoKeShi[0])
#                     else:
#                         fileResultContent = fileResultContent + '、{}'.format(keyNoKeShi[0])
#                 else:
#                     fileResultContent = fileResultContent + '{}'.format(keyNoKeShi[0])
#                 numCount = numCount + 1
#                 numLast = keyNoKeShi[1]
#             if numCount > 1:
#                 fileResultContent = fileResultContent + '各{}件'.format(numLast)
#             else:
#                 fileResultContent = fileResultContent + '{}件'.format(numLast)
#             fileResultContent = fileResultContent + '）。'
#         if numNoSheQu > 0:
#             fileResultContent = fileResultContent + '涉及社区（村）{}件（'.format(numNoSheQu)
#             dictNoSheQuNum = sorted(dictNoSheQuNum.items(), key=lambda kv: kv[1], reverse=True)
#             # logger.debug('{}'.format(dictNoSheQuNum))
#             numLastSheQu = 0
#             numIndexSheQu = 0
#             numCountSheQu = 0
#             for keyNoSheQu in dictNoSheQuNum:
#                 numIndexSheQu = numIndexSheQu + 1
#                 if numLastSheQu == 0:
#                     numLastSheQu = keyNoSheQu[1]
#                 else:
#                     if numLastSheQu != keyNoSheQu[1]:
#                         if numCountSheQu > 1:
#                             fileResultContent = fileResultContent + '各{}件，'.format(numLastSheQu)
#                         else:
#                             fileResultContent = fileResultContent + '{}件，'.format(numLastSheQu)
#                         numCountSheQu = 0
#                 if numLastSheQu == keyNoSheQu[1]:
#                     if numCountSheQu == 0:
#                         fileResultContent = fileResultContent + '{}'.format(keyNoSheQu[0])
#                     else:
#                         fileResultContent = fileResultContent + '、{}'.format(keyNoSheQu[0])
#                 else:
#                     fileResultContent = fileResultContent + '{}'.format(keyNoSheQu[0])
#                 numCountSheQu = numCountSheQu + 1
#                 numLastSheQu = keyNoSheQu[1]
#             if numCountSheQu > 1:
#                 fileResultContent = fileResultContent + '各{}件'.format(numLastSheQu)
#             else:
#                 fileResultContent = fileResultContent + '{}件'.format(numLastSheQu)
#             fileResultContent = fileResultContent + '）。详见附件3。'
#
#     # fileResultContent = fileResultContent + '\r\n主管领导失分情况\r\n'
#     #
#     # if numShiFen == 0:
#     #     fileResultContent = fileResultContent + "\r\n获取到本考核期裸分失分为0，无法计算占比，请核对是否正确！！！！！！！\r\n"
#     #     fileResultContent = fileResultContent + "获取到本考核期裸分失分为0，无法计算占比，请核对是否正确！！！！！！！\r\n"
#     #     fileResultContent = fileResultContent + "获取到本考核期裸分失分为0，无法计算占比，请核对是否正确！！！！！！！\r\n\r\n"
#     # fileResultContent = fileResultContent + "截至{}月{}日9时,".format(timeCur.month, timeCur.day)
#     # fileResultContent = fileResultContent + "本考核期裸分失分{}分，".format(numShiFen)
#     # fileResultContent = fileResultContent + "{}月{}日9时".format(datatimeYesterday.month, datatimeYesterday.day)
#     # fileResultContent = fileResultContent + '至{}月{}日9时，'.format(timeCur.month, timeCur.day)
#     # fileResultContent = fileResultContent + '区级有效回访工单{}件，'.format(numYouXiao)
#     # fileResultContent = fileResultContent + '其中双满工单{}件,失分工单{}件（'.format(numDoubleOk, numYouXiao-numDoubleOk)
#     # numWieJieJue = numNoKeShi + numNoSheQu - numJiBenManYi - numJieJueBuManYi
#     # if numWieJieJue == numYouXiao-numDoubleOk:
#     #     fileResultContent = fileResultContent + '均为未解决工单'
#     # else:
#     #     fileResultContent = fileResultContent + '未解决工单{}件'.format(numWieJieJue)
#     #     if numJiBenManYi > 0:
#     #         fileResultContent = fileResultContent + '，解决基本满意工单{}件'.format(numJiBenManYi)
#     #     if numJieJueBuManYi > 0:
#     #         fileResultContent = fileResultContent + '，解决不满意工单{}件'.format(numJieJueBuManYi)
#     # fileResultContent = fileResultContent + '）。'
#     #
#     # fileResultContent = fileResultContent + '\n失分工单{}件'.format(numYouXiao - numDoubleOk)
#     # lvShiFen = 0
#     # if numShiFen != 0:
#     #     lvShiFen = round(numShiFenCount*100/numShiFen, 2)
#     # fileResultContent = fileResultContent + '(共计失分{}分，占比{}%)，'.format(round(numShiFenCount, 2), lvShiFen)
#     # fileResultContent = fileResultContent + '主要涉及'
#     # listShiFenLingDaoValue = dict(sorted(listShiFenLingDaoValue.items(), key=lambda kv: kv[1], reverse=True))
#     # logger.debug('listShiFenLingDaoValue:{}'.format(listShiFenLingDaoValue))
#     # listShiFenLingDaoValueKeys = list(listShiFenLingDaoValue.keys())
#     # for indexLingDao in listShiFenLingDaoValueKeys:
#     #     if listShiFenLingDaoValueKeys.index(indexLingDao) != 0:
#     #         fileResultContent = fileResultContent + '，'
#     #     fileResultContent = fileResultContent + '{}{}件（'.format(indexLingDao.replace(' ', ''), listShiFenLingDaoCount[indexLingDao])
#     #     if numShiFen != 0:
#     #         fileResultContent = fileResultContent + '失分{}分，占比{}%）'.format(listShiFenLingDaoValue[indexLingDao], round(listShiFenLingDaoValue[indexLingDao]*100/numShiFen, 2))
#     #     else:
#     #         fileResultContent = fileResultContent + '失分{}分，占比%）'.format(listShiFenLingDaoValue[indexLingDao])
#     # fileResultContent = fileResultContent + '。\n经督办，'
#     # for indexLingDao in listShiFenLingDaoValueKeys:
#     #     if listShiFenLingDaoValueKeys.index(indexLingDao) != 0:
#     #         fileResultContent = fileResultContent + '；'
#     #     fileResultContent = fileResultContent + '{}{}件，'.format(indexLingDao.replace(' ', ''),
#     #                                                              listShiFenLingDaoCount[indexLingDao])
#     #     infoResult = listShiFenLingDaoInfo[indexLingDao]
#     #     if len(list(infoResult.keys())) == 1:
#     #         fileResultContent = fileResultContent + '{}'.format(list(infoResult.keys())[0])
#     #         continue
#     #     fileResultContent = fileResultContent + '其中'
#     #     infoResult = sorted(infoResult.items(), key=lambda kv: kv[1], reverse=True)
#     #     numLastSheQu = 0
#     #     numIndexSheQu = 0
#     #     numCountSheQu = 0
#     #     for keyNoSheQu in infoResult:
#     #         numIndexSheQu = numIndexSheQu + 1
#     #         if numLastSheQu == 0:
#     #             numLastSheQu = keyNoSheQu[1]
#     #         else:
#     #             if numLastSheQu != keyNoSheQu[1]:
#     #                 if numCountSheQu > 1:
#     #                     fileResultContent = fileResultContent + '各{}件，'.format(numLastSheQu)
#     #                 else:
#     #                     fileResultContent = fileResultContent + '{}件，'.format(numLastSheQu)
#     #                 numCountSheQu = 0
#     #         if numLastSheQu == keyNoSheQu[1]:
#     #             if numCountSheQu == 0:
#     #                 fileResultContent = fileResultContent + '{}'.format(keyNoSheQu[0])
#     #             else:
#     #                 fileResultContent = fileResultContent + '、{}'.format(keyNoSheQu[0])
#     #         else:
#     #             fileResultContent = fileResultContent + '{}'.format(keyNoSheQu[0])
#     #         numCountSheQu = numCountSheQu + 1
#     #         numLastSheQu = keyNoSheQu[1]
#     #     if numCountSheQu > 1:
#     #         fileResultContent = fileResultContent + '各{}件'.format(numLastSheQu)
#     #     else:
#     #         fileResultContent = fileResultContent + '{}件'.format(numLastSheQu)
#         # fileResultContent = fileResultContent + '；'
#         # infoResult = dict(sorted(infoResult.items(), key=lambda kv: kv[1], reverse=True))
#         # logger.debug('listShiFenLingDaoValue:{}'.format(listShiFenLingDaoValue))
#         # infoResultKeys = list(infoResult.keys())
#         # if len(infoResultKeys) > 1:
#         #     fileResultContent = fileResultContent + '其中'
#         #     numLastResult = 0
#         #     numXiangTong = 1
#         #     for infoResultIndex in infoResultKeys:
#         #         if infoResultKeys.index(infoResultIndex) != 0:
#         #             fileResultContent = fileResultContent + '，'
#         #         if infoResult[infoResultIndex] == numLastResult:
#         #             numXiangTong = numXiangTong + 1
#         #             fileResultContent = fileResultContent + '、'
#         #             fileResultContent = fileResultContent + '{}'.format(infoResultIndex)
#         #         else:
#         #             if numXiangTong > 1:
#         #                 numXiangTong = 1
#         #                 fileResultContent = fileResultContent + '各{}件'.format(numLastResult)
#         #             else:
#         #                 fileResultContent = fileResultContent + '{}件'.format(numLastResult)
#         #         numLastResult = infoResult[infoResultIndex]
#         # else:
#         #     fileResultContent = fileResultContent + '{}'.format(infoResultKeys[0])
#     # fileResultContent = fileResultContent + '。(详情见附件）'
#     logger.debug('fileMustDo:{}'.format(fileMustDo))
#     logger.debug('今日工单情况 和 工单回访情况:{}'.format(fileResultContent))
#     with open(fileMustDo, "w") as f:
#         f.write(fileResultContent)
#
# numColShiFouJieJue = hanchxl.get_col_index(sheetQuXiTongDaoChu, '是否解决\n是否满意')
# sheetQuXiTongDaoChu.delete_cols(numColShiFouJieJue)
# bookQuXiTongDaoChu.save(fileResultSave)


bookDengJiDanList = pd.read_excel(fileResultMuBanSave)
bookDengJiDanList.sort_values(by=['一级分类', '二级分类', '三级分类', '承办部门', '参与部门'], inplace=True,
                              ascending=[True, True, True, True, True])
bookDengJiDanList.to_excel(fileResultMuBanSave, index=False)

bookDengJiDanList = openpyxl.load_workbook(fileResultMuBanSave, data_only=True)
sheetDengJiDanList = bookDengJiDanList.active

logger.debug(sheetDengJiDanList.max_row)  # 打印表的行数
logger.debug(sheetDengJiDanList.max_column)  # 打印表的列数
maxRowDengJiDanList = sheetDengJiDanList.max_row
maxColDengJiDanList = sheetDengJiDanList.max_column

sheetDengJiDanList.insert_cols(1, 1)
sheetDengJiDanList.cell(1, 1).value = '是否失分'

fileZhouBaoSave = dirPath + '\\[内部] 拱辰街道接诉即办工作情况周报.docx'

# 打开周报模板文件
docMuBan = docx.Document(fileDengJiDanMuBan)

valueList = ['工单编号', '来电人', '工单类型', '工单状态', '详细地址', '一级分类', '三级分类', '标题', '主要内容',
             '派单时间',
             '办理结果', '截止日期', '承办单位', '办结时间', '七有五性', '回访状态', '是否见面', '是否满意', '接单部门',
             '来电时间',
             '来电号码', '联系方式', '派单类型', '二级分类', '标签', '回访时间', '不纳入类型', '是否联系', '是否解决',
             '所属辖区']

merged_document = docx.Document()
# composer = Composer(merged_document)

for row_index in range(2, maxRowDengJiDanList + 1):
    docMuBan = docx.Document(fileDengJiDanMuBan)
    colXuHao = hanchxl.get_col_index(sheetDengJiDanList, '序号')
    if colXuHao is not None:
        sheetDengJiDanList.cell(row_index, colXuHao).value = row_index - 1
        logger.debug('行：{}'.format(sheetDengJiDanList.cell(row_index, colXuHao).value))
    # docMuBan.add_page_break()
    colShiFouJieJue = hanchxl.get_col_index(sheetDengJiDanList, '是否解决')
    if colShiFouJieJue is None:
        logger.debug('未找到列：是否解决')
        continue
    colShiFouManYi = hanchxl.get_col_index(sheetDengJiDanList, '是否满意')
    if colShiFouManYi is None:
        logger.debug('未找到列：是否满意')
        continue
    valueJieJue = sheetDengJiDanList.cell(row_index, colShiFouJieJue).value
    valueManYi = sheetDengJiDanList.cell(row_index, colShiFouManYi).value
    if valueJieJue is None or valueManYi is None:
        logger.debug('该工单不是考核工单')
        continue
    if valueJieJue == '解决' or valueJieJue == '已解决':
        logger.debug('该工单为解决工单')
        continue
    sheetDengJiDanList.cell(row_index, 1).value = 1
    for table in docMuBan.tables:
        for row in table.rows:
            for cell in row.cells:
                for paragraph in cell.paragraphs:
                    for run in paragraph.runs:
                        logger.debug('{}'.format(run.text))
                        for valueIndex in valueList:
                            strReplace = '{' + '{}'.format(valueIndex) + '}'
                            if strReplace in run.text:
                                valueInfo = ''
                                if valueIndex == '来电号码' or valueIndex == '联系方式':
                                    colIndexValue = hanchxl.get_col_index(sheetDengJiDanList, '来电人电话/账号')
                                elif valueIndex == '详细地址':
                                    colIndexValue = hanchxl.get_col_index(sheetDengJiDanList, '地址')
                                elif valueIndex == '承办单位':
                                    colIndexValue = hanchxl.get_col_index(sheetDengJiDanList, '承办部门')
                                else:
                                    colIndexValue = hanchxl.get_col_index(sheetDengJiDanList, valueIndex)
                                logger.debug('{} : {}'.format(valueIndex, colIndexValue))
                                if colIndexValue is not None:
                                    valueInfo = sheetDengJiDanList.cell(row_index, colIndexValue).value
                                    if valueInfo is None:
                                        valueInfo = ''
                                    if valueIndex == '承办单位':
                                        valueCanYu = hanchxl.get_col_index(sheetDengJiDanList, '参与部门')
                                        if valueCanYu is not None:
                                            valueInfoCanYu = sheetDengJiDanList.cell(row_index, valueCanYu).value
                                            if valueInfoCanYu is not None:
                                                valueInfo = valueInfo + '    协办：' + valueInfoCanYu
                                run.text = run.text.replace(strReplace, '{}'.format(valueInfo))

    fileSaveDengJiDan = dirPath + '\\电话登记单-{}.docx'.format(row_index - 1)
    docMuBan.save(fileSaveDengJiDan)
    # composer.append(docMuBan)
maxRowDelete = maxRowDengJiDanList + 1
numShiFen = maxRowDengJiDanList
while maxRowDelete > 1:
    if sheetDengJiDanList.cell(maxRowDelete, 1).value != 1:
        sheetDengJiDanList.delete_rows(maxRowDelete)
        numShiFen = numShiFen - 1
    maxRowDelete = maxRowDelete - 1

# 合并 是否解决 和 是否满意
numColShiFouJieJue = hanchxl.get_col_index(sheetDengJiDanList, '是否解决')
sheetDengJiDanList.insert_cols(numColShiFouJieJue, 1)
sheetDengJiDanList.cell(1, numColShiFouJieJue).value = '是否解决\n是否满意'

logger.debug('合并 是否解决 和 是否满意')
hanchxl.he_bing_cols(sheetDengJiDanList, '是否解决', '是否满意', '是否解决\n是否满意')
numColShiFouJieJue = hanchxl.get_col_index(sheetDengJiDanList, '是否解决')
sheetDengJiDanList.insert_cols(numColShiFouJieJue, 1)
sheetDengJiDanList.cell(1, numColShiFouJieJue).value = '是否解决\n是否满意\n是否联系'
hanchxl.he_bing_cols(sheetDengJiDanList, '是否解决\n是否满意', '是否联系', '是否解决\n是否满意\n是否联系')
numColShiFouJieJue = hanchxl.get_col_index(sheetDengJiDanList, '是否解决')
sheetDengJiDanList.insert_cols(numColShiFouJieJue, 1)
sheetDengJiDanList.cell(1, numColShiFouJieJue).value = '回访结果'
hanchxl.he_bing_cols(sheetDengJiDanList, '是否解决\n是否满意\n是否联系', '是否见面', '回访结果')

# 删除不必要的列
logger.debug('删除不必要的列')
delColsList = ['序号', '工单编号', '承办部门', '参与部门', '标题', '主要内容', '来电人-合并', '办理结果', '回访结果',
               '派单时间', '派单类型', '来电人电话/账号']
hanchxl.remove_bu_bi_yao_col(sheetDengJiDanList, delColsList)

colSuQiuRen = hanchxl.get_col_index(sheetDengJiDanList, '来电人-合并')
sheetDengJiDanList.cell(1, colSuQiuRen).value = '来电人'

logger.debug('移动 来电人电话/账号 到 派单时间 后面')
hanchxl.move_col1_to_col2(sheetDengJiDanList, '来电人电话/账号', '回访结果', False)

colSuQiuRen = hanchxl.get_col_index(sheetDengJiDanList, '来电人')
sheetDengJiDanList.delete_cols(colSuQiuRen + 1)

colChengBan = hanchxl.get_col_index(sheetDengJiDanList, '承办部门')
sheetDengJiDanList.insert_cols(colChengBan, 1)
sheetDengJiDanList.cell(1, colChengBan).value = '承办领导'

colHuiFangJieGuo = hanchxl.get_col_index(sheetDengJiDanList, '回访结果')
sheetDengJiDanList.insert_cols(colHuiFangJieGuo + 1, 3)
sheetDengJiDanList.cell(1, colHuiFangJieGuo + 1).value = '督办结果'
sheetDengJiDanList.cell(1, colHuiFangJieGuo + 2).value = '进展情况'
sheetDengJiDanList.cell(1, colHuiFangJieGuo + 3).value = '书记批示'

maxColDengJiDanList = sheetDengJiDanList.max_column
sheetDengJiDanList.cell(1, maxColDengJiDanList + 1).value = '重复诉求'

bookShuJiPiShiHuiZong = openpyxl.load_workbook(fileShuJiPiShiHuiZong, data_only=True)
sheetPiShi = bookShuJiPiShiHuiZong.active
maxRowPiShi = sheetPiShi.max_row
maxColPiShi = sheetPiShi.max_column

maxRowDengJiDanList = sheetDengJiDanList.max_row
colLaiDianRenPiShi = hanchxl.get_col_index(sheetPiShi, '来电人', 2)
colGongDanCodePiShi = hanchxl.get_col_index(sheetPiShi, '工单编号', 2)
colPiShiRiQi = hanchxl.get_col_index(sheetPiShi, '批示日期', 2)
logger.debug('诉求人电话：在 {}列  工单编号在： {}列'.format(colLaiDianRenPiShi, colLaiDianRenPiShi))
colLaiDianRen = hanchxl.get_col_index(sheetDengJiDanList, '来电人电话/账号')
sheetDengJiDanList.cell(1, colLaiDianRen).value = '诉求人电话'
colChongFu = hanchxl.get_col_index(sheetDengJiDanList, '重复诉求')
for row in range(2, maxRowDengJiDanList + 1):
    valuePhone = '{}'.format(sheetDengJiDanList.cell(row, colLaiDianRen).value).strip()
    valueChongFu = ''
    if valuePhone != '' and valuePhone != 'None':
        for rowPiShi in range(3, maxRowPiShi + 1):
            valuePhonePiShi = '{}'.format(sheetPiShi.cell(rowPiShi, colLaiDianRenPiShi).value).strip()
            valueCodePiShi = '{}'.format(sheetPiShi.cell(rowPiShi, colGongDanCodePiShi).value).strip()
            valuePiShiRiQi = '{}'.format(sheetPiShi.cell(rowPiShi, colPiShiRiQi).value).strip()
            if valuePhonePiShi != '' and valuePhonePiShi.find(valuePhone) != -1:
                if valueChongFu == '':
                    valueChongFu = valueCodePiShi + '({})'.format(valuePiShiRiQi)
                else:
                    valueChongFu = valueChongFu + '; ' + valueCodePiShi + '({})'.format(valuePiShiRiQi)
    logger.debug('{} 行 杜求仁电话：{} 重复工单：{}'.format(row, valuePhone, valueChongFu))
    sheetDengJiDanList.cell(row, colChongFu).value = valueChongFu

maxRowDengJiDanList = sheetDengJiDanList.max_row
maxColDengJiDanList = sheetDengJiDanList.max_column
logger.debug('{}行 {}列'.format(maxRowDengJiDanList, maxColDengJiDanList))
fileSaveDengJiDanList = dirPath + '\\[内部] {}月{}日未解决工单明细({}件).xlsx'.format(timeCur.month, timeCur.day,
                                                                                      numShiFen)
bookDengJiDanList.save(fileSaveDengJiDanList)

# composer.save(fileSaveDengJiDan)




